Jassif Team Institution

let mysql = require('mysql');

let con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  let sql = "SELECT * FROM customers LIMIT 5";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

C:\Users\Your Name>node demo_db_limit.js

[
  { id: 1, name: 'John', address: 'Highway 71'},
  { id: 2, name: 'Peter', address: 'Lowstreet 4'},
  { id: 3, name: 'Amy', address: 'Apple st 652'},
  { id: 4, name: 'Hannah', address: 'Mountain 21'},
  { id: 5, name: 'Michael', address: 'Valley 345'}
]

let mysql = require('mysql');

let con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  let sql = "SELECT * FROM customers LIMIT 5 OFFSET 2";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

C:\Users\Your Name>node demo_db_offset.js

[
  { id: 3, name: 'Amy', address: 'Apple st 652'},
  { id: 4, name: 'Hannah', address: 'Mountain 21'},
  { id: 5, name: 'Michael', address: 'Valley 345'},
  { id: 6, name: 'Sandy', address: 'Ocean blvd 2'},
  { id: 7, name: 'Betty', address: 'Green Grass 1'}
]

let mysql = require('mysql');

let con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword",
  database: "mydb"
});

con.connect(function(err) {
  if (err) throw err;
  let sql = "SELECT * FROM customers LIMIT 2, 5";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

// Get top 5 customers by name
con.query("SELECT * FROM customers ORDER BY name ASC LIMIT 5", function (err, result) {
  if (err) throw err;
  console.log("Top 5 customers by name:", result);
});

// Get most recent 10 orders
con.query("SELECT * FROM orders ORDER BY order_date DESC LIMIT 10", function (err, result) {
  if (err) throw err;
  console.log("Most recent 10 orders:", result);
});

// Get first 3 active customers
con.query("SELECT * FROM customers WHERE status = 'active' LIMIT 3", function (err, result) {
  if (err) throw err;
  console.log("First 3 active customers:", result);
});

// Get limited results with complex conditions
con.query("SELECT * FROM products WHERE price > 100 AND category = 'electronics' LIMIT 5", function (err, result) {
  if (err) throw err;
  console.log("Expensive electronics (limited to 5):", result);
});

// Pagination with variables
const page = 2;
const pageSize = 10;
const offset = (page - 1) * pageSize;

let sql = "SELECT * FROM customers LIMIT ? OFFSET ?";
con.query(sql, [pageSize, offset], function (err, result) {
  if (err) throw err;
  console.log(`Page ${page} results:`, result);
});

// Dynamic pagination function
function getPaginatedResults(page, pageSize) {
  const offset = (page - 1) * pageSize;
  let sql = "SELECT * FROM customers ORDER BY id LIMIT ? OFFSET ?";
  
  con.query(sql, [pageSize, offset], function (err, result) {
    if (err) throw err;
    console.log(`Page ${page} (${pageSize} items):`, result);
  });
}

// Usage
getPaginatedResults(1, 5); // Page 1, 5 items
getPaginatedResults(2, 5); // Page 2, 5 items

// Get first record only
con.query("SELECT * FROM customers LIMIT 1", function (err, result) {
  if (err) throw err;
  console.log("First customer:", result[0]);
});

// Get last 5 records (assuming ordered by ID)
con.query("SELECT * FROM customers ORDER BY id DESC LIMIT 5", function (err, result) {
  if (err) throw err;
  console.log("Last 5 customers:", result);
});

// Get random 3 records
con.query("SELECT * FROM customers ORDER BY RAND() LIMIT 3", function (err, result) {
  if (err) throw err;
  console.log("Random 3 customers:", result);
});

// Get sample data for testing
con.query("SELECT * FROM products ORDER BY RAND() LIMIT 10", function (err, result) {
  if (err) throw err;
  console.log("Random product sample:", result);
});

// Get top product from each category
con.query(`
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank
    FROM products
  ) ranked 
  WHERE rank = 1
`, function (err, result) {
  if (err) throw err;
  console.log("Top product from each category:", result);
});

const mysql = require('mysql');

// Create connection
const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "company_db"
});

// Connect to MySQL
con.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  
  console.log('Connected to MySQL database');
  
  // Example 1: Basic LIMIT
  console.log('\n1. First 5 customers:');
  con.query("SELECT * FROM customers LIMIT 5", (err, result) => {
    if (err) {
      console.error('Error in query 1:', err.message);
      return;
    }
    console.log(result);
  });
  
  // Example 2: LIMIT with OFFSET
  console.log('\n2. Customers with OFFSET (records 3-7):');
  con.query("SELECT * FROM customers LIMIT 5 OFFSET 2", (err, result) => {
    if (err) {
      console.error('Error in query 2:', err.message);
      return;
    }
    console.log(result);
  });
  
  // Example 3: Shorter syntax LIMIT
  console.log('\n3. Customers with shorter syntax (records 3-7):');
  con.query("SELECT * FROM customers LIMIT 2, 5", (err, result) => {
    if (err) {
      console.error('Error in query 3:', err.message);
      return;
    }
    console.log(result);
  });
  
  // Example 4: Pagination system
  console.log('\n4. Pagination example:');
  function getCustomersPage(page, pageSize) {
    const offset = (page - 1) * pageSize;
    const sql = "SELECT * FROM customers ORDER BY id LIMIT ? OFFSET ?";
    
    con.query(sql, [pageSize, offset], (err, result) => {
      if (err) {
        console.error('Error in pagination:', err.message);
        return;
      }
      console.log(`Page ${page} (items ${offset + 1}-${offset + result.length}):`, result);
    });
  }
  
  // Get multiple pages
  getCustomersPage(1, 3); // Page 1: records 1-3
  getCustomersPage(2, 3); // Page 2: records 4-6
  
  // Example 5: Get total count for pagination
  console.log('\n5. Pagination with total count:');
  con.query("SELECT COUNT(*) as total FROM customers", (err, countResult) => {
    if (err) {
      console.error('Error getting count:', err.message);
      return;
    }
    
    const totalCustomers = countResult[0].total;
    const pageSize = 5;
    const totalPages = Math.ceil(totalCustomers / pageSize);
    
    console.log(`Total customers: ${totalCustomers}`);
    console.log(`Page size: ${pageSize}`);
    console.log(`Total pages: ${totalPages}`);
    
    // Close connection
    setTimeout(() => {
      con.end((err) => {
        if (err) {
          console.error('Error closing connection:', err.message);
          return;
        }
        console.log('\nConnection closed');
      });
    }, 2000);
  });
});